# -*- coding:utf-8 -*-
# @Time:2024/4/1921:01
# @Author:miuzg
# @FileName:new test2.py
# @Software:PyCharm

import openpyxl


class xlsx_generate:
    def __init__(self, xlsx_name, sheet_name, clear_list, judge):
        self.xlsx_name = xlsx_name
        self.sheet_name = sheet_name
        self.clear_list = clear_list
        self.wb = None
        self.ws = None
        self.judge = judge

    def workbook_generate(self):
        """生成工作簿"""
        self.wb = openpyxl.load_workbook(self.xlsx_name)
        self.ws = self.wb[self.sheet_name]

    def clear_data(self):
        """清洗数据"""

        header = next(self.ws.iter_rows(min_row=1, max_row=1))
        column_map = {cell.value: cell.column for cell in header}
        rows_to_delete = []

        # judge过滤操作
        if self.judge in column_map:
            judge_col = column_map[self.judge]


            for idx, row in enumerate(self.ws.iter_rows(min_row=2), start=2):
                cell = row[judge_col - 1]
                if cell.value not in (5, None, ''):
                    rows_to_delete.append(idx)

        for idx in reversed(rows_to_delete):
            self.ws.delete_rows(idx)

        # 选项赋值操作
        for col_header in self.clear_list:
            if col_header not in column_map:
                continue
            col_idx = column_map[col_header]
            for row in self.ws.iter_rows(min_row=2):
                cell = row[col_idx - 1]
                cell.value = 0 if cell.value in (None, '') else 1



    def save_xlsx(self):
        self.wb.save(f'clear_{self.xlsx_name}')

    def clear(self):
        self.workbook_generate()
        self.clear_data()
        self.save_xlsx()


if __name__ == '__main__':
    xlsx_name = 'maded_food.xlsx'
    sheet_name = '预制表格'
    clear_list = [f'S{i}' for i in range(1, 8)] + \
                 [f'C{i}' for i in range(1, 8)] + \
                 [f'P{i}' for i in range(1, 9)] + \
                 [f'A{i}' for i in range(1, 10)] + \
                 [f'Q{i}' for i in range(1, 7)]

gener = xlsx_generate(
    xlsx_name=xlsx_name,
    sheet_name=sheet_name,
    clear_list=clear_list,
    judge='judge')  # 假设列名为'judge'
gener.clear()
